package mysql;

import org.apache.commons.lang3.StringUtils;
import org.jsoup.helper.StringUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * SQL生成助手
 * Created by hcy on 2016/4/9.
 */
public class SQLGenerateHelper
{

    /**
     * 查询列
     */
    public static class Column
    {
        /**
         * 列名
         */
        public final String name;
        /**
         * 别名
         */
        public final String alias;

        public Column(String name, String alias)
        {
            this.name = name;
            this.alias = alias;
        }

    }

    /**
     * 查询条件
     */
    public static class Condition
    {


        /**
         * 列名
         */
        public final String name;

        /**
         * 条件
         */
        public final String condition;

        public Condition(String name, String condition)
        {
            this.name = name;
            this.condition = condition;
        }
    }



    /**
     * 列信息列表
     */
    private List<Column>    columns;
    /**
     * 查询条件
     */
    private List<Condition> conditions;

    /**
     * or语句查询条件
     */
    private List<Condition> orConditions;

    /**
     * and语句查询条件
     */
    private List<Condition> andConditions;

    /**
     * 参数列表
     */
    private List<Object>    paras;
    /**
     * 表名
     */
    private String          tableName;
    /**
     * 排序
     */
    private String          orderBy;

    private List<Condition> conditionGroup;

    /**
     * or 语句参数列表
     */
    private List<Object> orGroupParas;

    /**
     * and语句参数
     */
    private List<Object> andGroupParas;


    /**
     * 构造SQL生成助手
     */
    public SQLGenerateHelper()
    {
        this(null, null);
    }

    /**
     * 构造SQL生成助手
     *
     * @param tableName 查询表名
     */
    public SQLGenerateHelper(String tableName)
    {
        this(tableName, null);
    }

    /**
     * 构造SQL生成助手
     *
     * @param tableName 查询表名
     * @param orderBy   排序子句
     */
    public SQLGenerateHelper(String tableName, String orderBy)
    {
        this.tableName = tableName;
        this.columns = new ArrayList<>();
        this.paras = new ArrayList<>();
        this.orGroupParas = new ArrayList<>();
        this.andGroupParas = new ArrayList<>();
        this.conditions = new ArrayList<>();
        this.orConditions = new ArrayList<>();
        this.andConditions = new ArrayList<>();
        this.orderBy = orderBy;
    }

    /**
     * 添加查询列
     *
     * @param name 列名
     */
    public void addColumn(String name)
    {
        addColumn(name, name);
    }

    /**
     * 添加查询列
     *
     * @param name  列名
     * @param alias 别名
     */
    public void addColumn(String name, String alias)
    {
        this.columns.add(new Column(name, alias));
    }

    /**
     * 添加查询条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addEqualsCondition(String name, Object value)
    {
        this.addCondition(name, "=", value);
    }

    /**
     * 添加LIKE条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addLikeCondition(String name, Object value)
    {
        this.addCondition(name, "like", value);
    }

    /**
     * 添加IS NULL条件
     *
     * @param name 列名
     */
    public void addIsNullCondition(String name)
    {
        this.addCondition(name, "IS NULL", null);
    }

    /**
     * 添加IS NOT NULL条件
     *
     * @param name 列名
     */
    public void addIsNotNullCondition(String name)
    {
        this.addCondition(name, "IS NOT NULL", null);
    }

    public void addInCondition(String name, List<Integer> value)
    {
        this.addCondition(name, "in", StringUtils.join(value, ","));
    }
    public void addInConditionToLong(String name, List<Long> value)
    {
        this.addCondition(name, "in", StringUtils.join(value, ","));
    }





    /**
     * 添加OR语句 相等条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addOrEqualsCondition(String name, Object value)
    {
        this.addOrCondition(name, "=", value);
    }

    /**
     * 添加OR语句 LIKE条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addOrLikeCondition(String name, Object value)
    {
        this.addOrCondition(name, "like", value);
    }

    /**
     * 添加OR语句 IS NULL条件
     *
     * @param name 列名
     */
    public void addOrIsNullCondition(String name)
    {
        this.addOrCondition(name, "IS NULL", null);
    }

    /**
     * 添加OR语句 IS NOT NULL条件
     *
     * @param name 列名
     */
    public void addOrIsNotNullCondition(String name)
    {
        this.addOrCondition(name, "IS NOT NULL", null);
    }

    public void addOrInCondition(String name, List<Integer> value)
    {
        this.addOrCondition(name, "in", StringUtils.join(value, ","));
    }
    public void addOrInConditionToLong(String name, List<Long> value)
    {
        this.addOrCondition(name, "in", StringUtils.join(value, ","));
    }


    /**
     * 添加OR语句 相等条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addAndEqualsCondition(String name, Object value)
    {
        this.addAndCondition(name, "=", value);
    }

    /**
     * 添加OR语句 LIKE条件
     *
     * @param name  列名
     * @param value 值
     */
    public void addAndLikeCondition(String name, Object value)
    {
        this.addAndCondition(name, "like", value);
    }

    /**
     * 添加OR语句 IS NULL条件
     *
     * @param name 列名
     */
    public void addAndIsNullCondition(String name)
    {
        this.addAndCondition(name, "IS NULL", null);
    }

    /**
     * 添加OR语句 IS NOT NULL条件
     *
     * @param name 列名
     */
    public void addAndIsNotNullCondition(String name)
    {
        this.addAndCondition(name, "IS NOT NULL", null);
    }

    public void addAndInCondition(String name, List<Integer> value)
    {
        this.addAndCondition(name, "in", StringUtils.join(value, ","));
    }
    public void addAndInConditionToLong(String name, List<Long> value)
    {
        this.addAndCondition(name, "in", StringUtils.join(value, ","));
    }



    /**
     * 添加查询条件
     *
     * @param name      列名
     * @param condition 条件
     * @param value     值
     */
    public void addCondition(String name, String condition, Object value)
    {
        if (StringUtils.isNotBlank(name) &&
                StringUtils.isNotBlank(condition))
        {
            if(null !=  value && StringUtils.isNotBlank(value.toString()))
            {
                if (condition.equalsIgnoreCase("like"))
                {
                    paras.add("%" + value.toString() + "%");
                } else if (condition.equals("in"))
                {
                    paras.add("(" + value + ")");
                } else
                {
                    paras.add(value);
                }
                this.conditions.add(new Condition(name, condition));
            } else if(condition.equalsIgnoreCase("IS NULL") || condition.equalsIgnoreCase("IS NOT NULL")) {
                this.conditions.add(new Condition(name, condition));
            }
        }
    }

    /**
     * 添加OR查询条件
     *
     * @param name      列名
     * @param condition 条件
     * @param value     值
     */
    public void addOrCondition(String name, String condition, Object value)
    {
        if (StringUtils.isNotBlank(name) &&
                StringUtils.isNotBlank(condition))
        {
            if(null !=  value && StringUtils.isNotBlank(value.toString()))
            {
                if (condition.equalsIgnoreCase("like"))
                {
                    orGroupParas.add("%" + value.toString() + "%");
                } else if (condition.equals("in"))
                {
                    orGroupParas.add("(" + value + ")");
                } else
                {
                    orGroupParas.add(value);
                }
                this.orConditions.add(new Condition(name, condition));
            } else if(condition.equalsIgnoreCase("IS NULL") || condition.equalsIgnoreCase("IS NOT NULL")) {
                this.orConditions.add(new Condition(name, condition));
            }
        }
    }

    /**
     * 添加AND查询条件
     *
     * @param name      列名
     * @param condition 条件
     * @param value     值
     */
    public void addAndCondition(String name, String condition, Object value)
    {
        if (StringUtils.isNotBlank(name) &&
                StringUtils.isNotBlank(condition))
        {
            if(null !=  value && StringUtils.isNotBlank(value.toString()))
            {
                if (condition.equalsIgnoreCase("like"))
                {
                    andGroupParas.add("%" + value.toString() + "%");
                } else if (condition.equals("in"))
                {
                    andGroupParas.add("(" + value + ")");
                } else
                {
                    andGroupParas.add(value);
                }
                this.andConditions.add(new Condition(name, condition));
            } else if(condition.equalsIgnoreCase("IS NULL") || condition.equalsIgnoreCase("IS NOT NULL")) {
                this.andConditions.add(new Condition(name, condition));
            }
        }
    }

    /**
     * 得到查询SELECT SQL
     *
     * @return SELECT SQL
     */
    public String getSqlSelect()
    {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        if (this.columns.size() == 0)
        {
            sql.append("*");
        } else
        {
            for (int i = 0; i < this.columns.size(); i++)
            {
                if (i > 0)
                {
                    sql.append(",");
                }
                sql.append(this.columns.get(i).name).append(" ").append(this.columns.get(i).alias);
            }
        }
        return sql.toString().replaceAll("\\s+", " ");
    }

    /**
     * 得到除了SELECT之外的SQL
     *
     * @return 除了SELECT之外的SQL
     */
    public String getSqlExceptSelect()
    {
        StringBuilder sql = new StringBuilder();
        sql.append(" FROM ").append(tableName);
        if (conditions.size() > 0)
        {
            sql.append(getSqlWhere());
        }
        sql.append(getSqlOrderBy());
        return sql.toString().replaceAll("\\s+", " ");
    }

    public String getSqlExceptSelect2()
    {
        StringBuilder sql = new StringBuilder();
        sql.append(" FROM ").append(tableName);
        /*if (conditions.size() > 0)
        {
            sql.append(getSqlWhere());
        }*/
        sql.append(getSqlWhere2());
        sql.append(getSqlOrderBy());
        return sql.toString().replaceAll("\\s+", " ");
    }

    public String getSqlWhere()
    {
        StringBuilder sql      = new StringBuilder();
        int           paraSize = conditions.size();
        if (paraSize > 0)
        {

            for (int i = 0; i < paraSize; i++)
            {
                if (i > 0)
                {
                    sql.append(" AND ");
                }
                sql.append(conditions.get(i).name);
                if (conditions.get(i).condition.equalsIgnoreCase("in"))
                {
                    sql.append(" ").append(conditions.get(i).condition).append(" ").append(paras.get(i)).append(" ");
                    paras.remove(i);
                } else if(conditions.get(i).condition.equalsIgnoreCase("IS NULL")){
                    sql.append(" IS NULL ");
                } else if(conditions.get(i).condition.equalsIgnoreCase("IS NOT NULL")){
                    sql.append(" IS NOT NULL ");
                } else
                {
                    sql.append(" ").append(conditions.get(i).condition).append(" ");
                    sql.append(" ? ");
                }
            }
        }

        return sql.toString();
    }

    public String getSqlWhere2(){
        StringBuilder sql      = new StringBuilder();
        if(orGroupParas.size()>0 || andGroupParas.size() >0 || paras.size() >0){
            sql.append(" WHERE ");
        }

        String orGroup = getSqlOrWhere();
        String orTag = " ";
        if(StringUtils.isNotBlank(orGroup)){
            sql.append(" "+ orGroup +" ");
            orTag = " AND ";
        }

        String andGroup = getSqlAndWhere();
        String andTag = " ";
        if(StringUtils.isNotBlank(andGroup)){
            if(orTag.trim().length() > 0){
                sql.append(orTag);
                sql.append(" "+andGroup+" ");
                andTag = " AND ";
            }else{
                sql.append(" "+andGroup+" ");
                andTag = " AND ";
            }
        }

        String sqlWhere = getSqlWhere();
        if(StringUtils.isNotBlank(sqlWhere)){
            if(andTag.trim().length()>0  ){
                sql.append(andTag);
                sql.append(" "+sqlWhere+" ");
            }else{
                if(orTag.trim().length()>0){
                    sql.append(orTag);
                    sql.append(" "+sqlWhere+" ");
                }else{
                    sql.append(" "+sqlWhere+" ");
                }
            }
        }

        return sql.toString();
    }

    public String getSqlOrderBy()
    {
        StringBuilder sql = new StringBuilder();
        if (null != orderBy)
        {
            sql.append(" ORDER BY ").append(orderBy);
        }
        return sql.toString();
    }

    public String getSqlOrWhere()
    {
        StringBuilder sql      = new StringBuilder();
        int           paraSize = orConditions.size();
        if (paraSize > 0)
        {
            sql.append(" ( ");
            for (int i = 0; i < paraSize; i++)
            {
                if (i > 0)
                {
                    sql.append(" OR ");
                }
                sql.append(orConditions.get(i).name);
                if (orConditions.get(i).condition.equalsIgnoreCase("in"))
                {
                    sql.append(" ").append(orConditions.get(i).condition).append(" ").append(orGroupParas.get(i)).append(" ");
                    orGroupParas.remove(i);
                } else if(orConditions.get(i).condition.equalsIgnoreCase("IS NULL")){
                    sql.append(" IS NULL ");
                } else if(orConditions.get(i).condition.equalsIgnoreCase("IS NOT NULL")){
                    sql.append(" IS NOT NULL ");
                } else
                {
                    sql.append(" ").append(orConditions.get(i).condition).append(" ");
                    sql.append(" ? ");
                }
            }
            sql.append(" ) ");
        }

        return sql.toString();
    }

    public String getSqlAndWhere()
    {
        StringBuilder sql      = new StringBuilder();
        int           paraSize = andConditions.size();
        if (paraSize > 0)
        {
            sql.append(" ( ");
            for (int i = 0; i < paraSize; i++)
            {
                if (i > 0)
                {
                    sql.append(" AND ");
                }
                sql.append(andConditions.get(i).name);
                if (andConditions.get(i).condition.equalsIgnoreCase("in"))
                {
                    sql.append(" ").append(andConditions.get(i).condition).append(" ").append(andGroupParas.get(i)).append(" ");
                    andGroupParas.remove(i);
                } else if(andConditions.get(i).condition.equalsIgnoreCase("IS NULL")){
                    sql.append(" IS NULL ");
                } else if(andConditions.get(i).condition.equalsIgnoreCase("IS NOT NULL")){
                    sql.append(" IS NOT NULL ");
                } else
                {
                    sql.append(" ").append(andConditions.get(i).condition).append(" ");
                    sql.append(" ? ");
                }
            }
            sql.append(" ) ");
        }

        return sql.toString();
    }

    /**
     * 得到查询参数
     * @return 查询参数
     */
    public Object[] getQueryParas2()
    {
        List<Object> list = new ArrayList<>();
        return paras.toArray();
    }

    public Object[] getQueryParas(){
        List<Object> listPara = new ArrayList<>();
        for(Object oo : orGroupParas){
            listPara.add(oo);
        }

        for(Object oo : andGroupParas){
            listPara.add(oo);
        }

        for(Object oo : paras){
            listPara.add(oo);
        }

        return listPara.toArray();
    }


}
